{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#第一二三问\n",
    "import pyexcel as pe\n",
    "import pandas as pd\n",
    "import re\n",
    "sheet = pe.get_sheet(file_name = 'C:/Users/Mr.Blue/Desktop/gaze.csv')\n",
    "\n",
    "data = []\n",
    "res = []\n",
    "sigmax = 0\n",
    "sigmay = 0\n",
    "meanx = 0\n",
    "meany = 0\n",
    "\n",
    "for i in sheet:\n",
    "    meanx+=i[3]\n",
    "    meany+=i[4]\n",
    "meanx/=87442\n",
    "meany/=87442\n",
    "\n",
    "for i in sheet:\n",
    "    sigmax+=(i[3]-meanx)**2\n",
    "    sigmay+=(i[3]-meany)**2\n",
    "sigmax/=87442\n",
    "sigmay/=87442\n",
    "\n",
    "def datetime(time):\n",
    "    time = str(time).split('.')\n",
    "    ans=str(pd.to_datetime(time[0],unit='s')).replace(' ','T')+'.'+str(time[1])+'+0000'\n",
    "    if int(ans.split(':',1)[0][-2:])+10>=24:\n",
    "        ans = ans.split('-')\n",
    "        year = ans[0]\n",
    "        amount = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]\n",
    "        if year % 400 == 0 or (year % 4 == 0 and year % 100 != 0):\n",
    "            amount[1] += 1\n",
    "        if int(ans[2])[:2]==amount[int(ans[1])-1]:\n",
    "            if ans[1] == '12':\n",
    "                ans[0] = str(int(ans[0])+1)\n",
    "            else:\n",
    "                ans[1] = str(int(ans[1])+1)\n",
    "        ans = \"-\".join(ans)\n",
    "    return ans\n",
    "\n",
    "for i in sheet:\n",
    "    if i[3]>meanx+3*sigmax or i[3]<meanx-3*sigmax or i[4]>meany+3*sigmay or i[4]<meany-3*sigmax:\n",
    "        pass\n",
    "    else:\n",
    "        res.append([datetime(i[0]),i[1],i[2],i[3],i[4]]+i[5:])\n",
    "pe.save_as(array=res, dest_file_name='C:/Users/Mr.Blue/Desktop/ans.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyexcel as pe\n",
    "import re\n",
    "sheet = pe.get_sheet(file_name = 'C:/Users/Mr.Blue/Desktop/ans.xlsx')\n",
    "x=re.split('\\:|\\+',sheet[0,0])\n",
    "x=[float(i) for i in x[1:3]]\n",
    "res = [0,]\n",
    "for i in sheet.column[0]:\n",
    "    y = re.split('\\:|\\+',i)\n",
    "    y=[float(i) for i in y[1:3]]\n",
    "    if y[0] > x[0] and y[1] > x[1]:\n",
    "        res.append(0)\n",
    "        x[0]+=1\n",
    "    else:\n",
    "        res[-1]+=1\n",
    "hz = sum(res)/len(res)\n",
    "print(hz)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyexcel as pe\n",
    "import re\n",
    "sheet = pe.get_sheet(file_name = 'C:/Users/Mr.Blue/Desktop/ans.xlsx')\n",
    "time=re.split('\\:|\\+',sheet[0,0])\n",
    "x=float(time[2])+0.01\n",
    "res = [sheet.row[0]]\n",
    "for i in range(1,len(sheet)-1):\n",
    "    y = re.split('\\:|\\+',sheet[i,0])\n",
    "    y=float(y[2])\n",
    "    z = re.split('\\:|\\+',sheet[i+1,0])\n",
    "    z=float(z[2])\n",
    "    if y <= x and x <= z :\n",
    "        if abs(y-x) <= abs(z-x):\n",
    "            res.append(sheet.row[i])\n",
    "        else:\n",
    "            res.append(sheet.row[i+1])\n",
    "        x+=0.01\n",
    "        if x >= 60:\n",
    "            x-=60\n",
    "pe.save_as(array=res, dest_file_name='C:/Users/Mr.Blue/Desktop/ans5.xlsx')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
